﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Diagnostics;
using You2V.LocalDBLayer;
using System.Data;
using You2V.Data.Shared;

namespace You2v.DataMigrationTool
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
            if (MessageBox.Show("本程序将清空所有Student2012学生数据，从后台数据库转换一部分实例数据供测试，是否继续？",
                            "数据初始化",
                            MessageBoxButton.YesNo)
                     == MessageBoxResult.No)
            {
                this.Close();
                return;
            }

            string ConvertDetail = string.Empty;
            if (ConvertStudentData(ref ConvertDetail))
                this.MigrationResult.Text = "转换成功\r\n\r\n" + ConvertDetail;
            else
                this.MigrationResult.Text = "转换失败";
        }

        public bool ConvertStudentData(ref string ConvertDetail)
        {
            string strQuery = "DELETE FROM Student2012";

            if (!DBAccess.RunCommand(strQuery))
                return false;

            strQuery = "DELETE FROM StudentFamilyMembers";

            if (!DBAccess.RunCommand(strQuery))
                return false;

            strQuery = "DELETE FROM stu_edu_info";

            if (!DBAccess.RunCommand(strQuery))
                return false;

            strQuery = " SELECT "
                        + "tc_id,  \r\n"
                        + "tc_area,   \r\n"
                        + "tc_name,     \r\n"
                        //+ "left(tc_name,len(tc_name)-2) as StudentNumber,  \r\n"
                        //+ "\"*\"+right(tc_name,2)+\"*\" as FullName,  \r\n"
                        + "right(tc_name,2) as NickName,  \r\n"
                        + "tc_sex as Gender,  \r\n"
                        + "null as BirthDate,  \r\n"
                        + "tc_y3 as LiveInSchool,  \r\n"
                        + "tc_indate as Nation,  \r\n"
                        + "tc_dw as SchoolShortName,  \r\n"
                        + "tc_dw as SchoolFullName,  \r\n"
                        + "tc_address as ClassName,  \r\n"
                        + "tc_zhy as StudentAddressPostCode,  \r\n"
                        + "\"\" as StudentAddressText,  \r\n"
                        + "\"\" as HomeAddressPostCode,  \r\n"
                        + "tc_xl_ade as HomeDetailAddressText,  \r\n"
                        + "tc_xl_ade as HomeAddressTextForPublish,  \r\n"
                        + "tc_work_jx as HomeIncomeSummary,  \r\n"
                        + "tc_work_ky as HouseStatus,  \r\n"
                        + "tc_work_xsh as AssetStatus,  \r\n"
                        + "tc_shh_xsh_zhw1 as SponsorSuggestion,  \r\n"
                        + "tc_shh_xsh_name1 as RewardsJustGain,  \r\n"
                        + "tc_zyz1 as Volunteer,  \r\n"
                        + "tc_bzh as VolunteerReport,  \r\n"
                        + "tc_bzh1 as SelfIntroduction,  \r\n"
                        + "tc_bzh2 as TeacherFeedback,  \r\n"
                        + "tc_picaex as Picture1Title, \r\n"
                        + "tc_pica as Picture1URL_v1, \r\n"
                        + "\"\" as Picture1URL_2012, \r\n"
                        + "tc_picbex as Picture2Title, \r\n"
                        + "tc_picb as Picture2URL_v1, \r\n"
                        + "\"\" as Picture2URL_2012, \r\n"
                        + "tc_up, \r\n"
                        + "tc_status, \r\n"
                        + "tc_xl_bylx1,tc_xl_bylx2,tc_xl_bylx3,tc_xl_bylx4,tc_xl_bylx5,  \r\n"          //关系
                        + "tc_xl_bydate1, tc_xl_bydate2, tc_xl_bydate3, tc_xl_bydate4, tc_xl_bydate5, \r\n"     //姓名
                        + "tc_xl_byo1, tc_xl_byo2, tc_xl_byo3, tc_xl_byo4, tc_xl_byo5, \r\n"        //年龄
                        + "tc_xl_byschool1, tc_xl_byschool2, tc_xl_byschool3, tc_xl_byschool4, tc_xl_byschool5, \r\n"       //职业
                        + "tc_xl_he1, tc_xl_he2, tc_xl_he3, tc_xl_he4, tc_xl_he5, \r\n"     //健康情况
                        + "tc_xl_zhy1, tc_xl_zhy2, tc_xl_zhy3, tc_xl_zhy4, tc_xl_zhy5 \r\n"     //外出打工情况
                        + " from tcinfor \r\n"
                        //+ " where len(tc_name) in (9,10) AND left(tc_name,2) in ('MS','WX', 'DL') \r\n"    //todo
                        + " order by tc_id ";

            DataTable StudentList = DBAccess.GetDataTable(strQuery);
            if (StudentList == null)
                return false;

            ConvertDetail = string.Empty;

            foreach (DataRow row in StudentList.Rows)
            {
                //Debug.WriteLine("开始转换学生tc_id: \t" + row["tc_id"]);

                Guid StudentID = Guid.NewGuid();

                string StudentAddressPostCode = (string)row["StudentAddressPostCode"];
                StudentAddressPostCode = StudentAddressPostCode.Trim();
                if (StudentAddressPostCode.Length > 6)
                {
                    string Errror = "学生tc_id(" + row["tc_id"] + ")的邮编(" + StudentAddressPostCode + ")超过6位,目前只取前六位，请核对数据以后重新导入";
                    Debug.WriteLine(Errror);
                    ConvertDetail += Errror + "\r\n";
                    StudentAddressPostCode = StudentAddressPostCode.Substring(0, 6);
                }
                else if (StudentAddressPostCode.Length > 0 && StudentAddressPostCode.Length < 6)    //长度0不提示了
                {
                    string Errror = "学生tc_id(" + row["tc_id"] + ")的邮编(" + StudentAddressPostCode + ")不足6位,请核对数据以后重新导入";
                    Debug.WriteLine(Errror);
                    ConvertDetail += Errror + "\r\n";
                }

                string LiveInSchool = (string)row["LiveInSchool"];
                if (LiveInSchool.Length > 1)
                {
                    string Errror = "学生tc_id(" + row["tc_id"] + ")的是否住校(" + LiveInSchool + ")超过1位,请核对数据以后重新导入";
                    Debug.WriteLine(Errror);
                    ConvertDetail += Errror + "\r\n";
                    
                    LiveInSchool = "-";     //未知
                }

                string StudentNumber = string.Empty;
                string FullName = string.Empty;
                string NickName = string.Empty;
                string tc_name = (string)row["tc_name"];
                tc_name = tc_name.ToUpperInvariant();

                if (tc_name.Length >= 6 
                    && tc_name[0] >= 'A' && tc_name[0] <= 'Z')
                {
                    int i;
                    for (i = 3; i < tc_name.Length; i++)
                    {
                        if ((tc_name[i] >= '0' && tc_name[i] <= '9')
                            || (tc_name[i] >= 'A' && tc_name[i] <= 'Z'))
                        {
                            continue;
                        }
                        else
                        {
                            break;
                        }
                    }

                    StudentNumber = tc_name.Substring(0, i);

                    if (i < tc_name.Length)
                    {
                        // 编号+别名方式，当前使用方法，2012年还在使用
                        NickName = tc_name.Substring(i);
                        FullName = "*" + NickName + "*";
                    }
                    else
                    {
                        //只有编号没有别名，例如TY05035
                        Debug.WriteLine("只有编号，没有名字:\t" + tc_name);
                        NickName = tc_name;
                        FullName = "*" + tc_name + "*";
                    }
                }
                else if (!(tc_name[0] >='0' && tc_name[0] <='9')
                    && !(tc_name[0] >='A' && tc_name[0] <='Z'))
                {
                    Debug.WriteLine("只有名字，没有编号:\t" + tc_name);
                    // 真实名字
                    StudentNumber = tc_name;
                    NickName = tc_name;
                    FullName = tc_name;
                }

                if (string.IsNullOrWhiteSpace(StudentNumber)
                    || string.IsNullOrWhiteSpace(FullName)
                    || string.IsNullOrWhiteSpace(NickName))
                {
                    ConvertDetail += "导入学生失败, tc_id=" + row["tc_id"].ToString() + "\t姓名=\t" + tc_name  + "\r\n";
                    Debug.WriteLine("导入学生失败, tc_id=" + row["tc_id"].ToString() + "\t姓名=\t" + tc_name);
                    continue;
                }

                string tc_up = (string)row["tc_up"];
                if (row["tc_id"].ToString().Equals("1835"))
                {
                    tc_up = "2";  //不知道为何数据为空，不过学生信息已经发布 http://www.you2v.com/page/student/lingyun/detail.asp?id=1835&areaid=9
                }

                string strCommand = //" INSERT INTO Student2012 (ID,tc_id,tc_area,StudentNumber,FullName,NickName,Gender,BirthDate,LiveInSchool,Nation,SchoolShortName,SchoolFullName,"

                    // todo, birthdate
                    " INSERT INTO Student2012 (ID,tc_id,tc_area,StudentNumber,FullName,NickName,Gender,LiveInSchool,Nation,SchoolShortName,SchoolFullName,"
                                    + "ClassName,StudentAddressPostCode,StudentAddressText,"
                                    + "HomeAddressPostCode,HomeDetailAddressText,HomeAddressTextForPublish,"
                                    + "HomeIncomeSummary,HouseStatus,AssetStatus,SponsorSuggestion,RewardsJustGain,"
                                    + "Volunteer,VolunteerReport,SelfIntroduction,TeacherFeedback,"
                                    + "Picture1Title,Picture1URL_v1,Picture1URL_2012,Picture2Title,Picture2URL_v1,Picture2URL_2012,"
                                    + "UploadStatus,SponsorStatus,SponsorShipTracking) \r\n"
                                    + "VALUES( \r\n"
                                    + " {" + StudentID.ToString() + "}, \r\n"
                                    + row["tc_id"] + ", \r\n"
                                    + " '" + row["tc_area"] + "', \r\n"
                                    + " '" + StudentNumber + "', \r\n"
                                    + " '" + FullName + "', \r\n"
                                    + " '" + NickName + "', \r\n"
                                    + " '" + row["Gender"] + "', \r\n"
                                    //+ " '" + row["BirthDate"] + "', \r\n"
                                    + " '" + LiveInSchool + "', \r\n"
                                    + " '" + row["Nation"] + "', \r\n"
                                    + " '" + row["SchoolShortName"] + "', \r\n"
                                    + " '" + row["SchoolFullName"] + "', \r\n"
                                    + " '" + row["ClassName"] + "', \r\n"
                                    //+ " '" + row["StudentAddressPostCode"] + "', \r\n"
                                    + " '" + StudentAddressPostCode + "', \r\n"
                                    + " '" + row["StudentAddressText"] + "', \r\n"
                                    + " '" + row["HomeAddressPostCode"] + "', \r\n"
                                    + " '" + row["HomeDetailAddressText"] + "', \r\n"
                                    + " '" + row["HomeAddressTextForPublish"] + "', \r\n"
                                    + " '" + row["HomeIncomeSummary"] + "', \r\n"
                                    + " '" + row["HouseStatus"] + "', \r\n"
                                    + " '" + row["AssetStatus"] + "', \r\n"
                                    + " '" + row["SponsorSuggestion"] + "', \r\n"
                                    + " '" + row["RewardsJustGain"] + "', \r\n"
                                    + " '" + row["Volunteer"] + "', \r\n"
                                    + " '" + row["VolunteerReport"] + "', \r\n"
                                    + " '" + row["SelfIntroduction"] + "', \r\n"
                                    + " '" + row["TeacherFeedback"] + "', \r\n"
                                    + " '" + row["Picture1Title"] + "', \r\n"
                                    + " '" + row["Picture1URL_v1"] + "', \r\n"
                                    + " '" + row["Picture1URL_2012"] + "', \r\n"
                                    + " '" + row["Picture2Title"] + "', \r\n"
                                    + " '" + row["Picture2URL_v1"] + "', \r\n"
                                    + " '" + row["Picture2URL_2012"] + "', \r\n"
                                    + tc_up + ", \r\n"      //注意不要用'', 因为2012表里面是整数
                                    + row["tc_status"] + ", \r\n"
                                    + " '无跟踪信息' \r\n"            //todo, 需要用资助进度表更新过来
                                    + " ) \r\n";

                if (!DBAccess.RunCommand(strCommand))
                {
                    Debug.WriteLine("运行语句\r\n" + strCommand);
                    ConvertDetail += "导入失败, tc_id=" + row["tc_id"].ToString() + "\r\n";
                    Debug.WriteLine("导入失败, tc_id=" + row["tc_id"].ToString() + "\r\n");
                    continue;
                }

                //读取家庭成员
                int SequenceNumber = 0;
                if (!string.IsNullOrWhiteSpace((string)row["tc_xl_bylx1"]))
                {
                    SequenceNumber++;
                    Guid FamilyMemberID = Guid.NewGuid();

                    //todo, 该表不需要StudentNumber， 不过留着以后看着方便
                    strCommand = "INSERT INTO StudentFamilyMembers(ID, StudentID, SequenceNumber, RelationShip, FullName, Age, Job, HealthStatus, JobOutOfHometown ) \r\n"
                                + " Values ( \r\n"
                                + "{" + FamilyMemberID + "}, \r\n"
                                + "{" + StudentID + "}, \r\n"
                                + SequenceNumber + ", \r\n"
                                + "'" + row["tc_xl_bylx1"] +"', \r\n"
                                + "'" + row["tc_xl_bydate1"] +"', \r\n"
                                + "'" + row["tc_xl_byo1"] +"', \r\n"
                                + "'" + row["tc_xl_byschool1"] + "', \r\n"
                                + "'" + row["tc_xl_he1"] + "', \r\n"
                                + "'" + row["tc_xl_zhy1"] + "' \r\n"
                                + " ) \r\n";

                    if (!DBAccess.RunCommand(strCommand))
                    {
                        ConvertDetail += "导入家庭成员1失败, tc_id=" + row["tc_id"].ToString() + "\r\n";
                        Debug.WriteLine("导入家庭成员1失败, tc_id=" + row["tc_id"].ToString() + "\r\n");
                        continue;
                    }
                }

                if (!string.IsNullOrWhiteSpace((string)row["tc_xl_bylx2"]))
                {
                    SequenceNumber++;
                    Guid FamilyMemberID = Guid.NewGuid();

                    //todo, 该表不需要StudentNumber， 不过留着以后看着方便
                    strCommand = "INSERT INTO StudentFamilyMembers(ID, StudentID, SequenceNumber, RelationShip, FullName, Age, Job, HealthStatus, JobOutOfHometown ) \r\n"
                                + " Values ( \r\n"
                                + "{" + FamilyMemberID + "}, \r\n"
                                + "{" + StudentID + "}, \r\n"
                                + SequenceNumber + ", \r\n"
                                + "'" + row["tc_xl_bylx2"] + "', \r\n"
                                + "'" + row["tc_xl_bydate2"] + "', \r\n"
                                + "'" + row["tc_xl_byo2"] + "', \r\n"
                                + "'" + row["tc_xl_byschool2"] + "', \r\n"
                                + "'" + row["tc_xl_he2"] + "', \r\n"
                                + "'" + row["tc_xl_zhy2"] + "' \r\n"
                                + " ) \r\n";

                    if (!DBAccess.RunCommand(strCommand))
                    {
                        ConvertDetail += "导入家庭成员2失败, tc_id=" + row["tc_id"].ToString() + "\r\n";
                        Debug.WriteLine("导入家庭成员2失败, tc_id=" + row["tc_id"].ToString() + "\r\n");
                        continue;
                    }
                }

                if (!string.IsNullOrWhiteSpace((string)row["tc_xl_bylx3"]))
                {
                    SequenceNumber++;
                    Guid FamilyMemberID = Guid.NewGuid();

                    //todo, 该表不需要StudentNumber， 不过留着以后看着方便
                    strCommand = "INSERT INTO StudentFamilyMembers(ID, StudentID, SequenceNumber, RelationShip, FullName, Age, Job, HealthStatus, JobOutOfHometown ) \r\n"
                                + " Values ( \r\n"
                                + "{" + FamilyMemberID + "}, \r\n"
                                + "{" + StudentID + "}, \r\n"
                                + SequenceNumber + ", \r\n"
                                + "'" + row["tc_xl_bylx3"] + "', \r\n"
                                + "'" + row["tc_xl_bydate3"] + "', \r\n"
                                + "'" + row["tc_xl_byo3"] + "', \r\n"
                                + "'" + row["tc_xl_byschool3"] + "', \r\n"
                                + "'" + row["tc_xl_he3"] + "', \r\n"
                                + "'" + row["tc_xl_zhy3"] + "' \r\n"
                                + " ) \r\n";

                    if (!DBAccess.RunCommand(strCommand))
                    {
                        ConvertDetail += "导入家庭成员3失败, tc_id=" + row["tc_id"].ToString() + "\r\n";
                        Debug.WriteLine("导入家庭成员3失败, tc_id=" + row["tc_id"].ToString() + "\r\n");
                        continue;
                    }
                }

                if (!string.IsNullOrWhiteSpace((string)row["tc_xl_bylx4"]))
                {
                    SequenceNumber++;
                    Guid FamilyMemberID = Guid.NewGuid();

                    //todo, 该表不需要StudentNumber， 不过留着以后看着方便
                    strCommand = "INSERT INTO StudentFamilyMembers(ID, StudentID, SequenceNumber, RelationShip, FullName, Age, Job, HealthStatus, JobOutOfHometown ) \r\n"
                                + " Values ( \r\n"
                                + "{" + FamilyMemberID + "}, \r\n"
                                + "{" + StudentID + "}, \r\n"
                                + SequenceNumber + ", \r\n"
                                + "'" + row["tc_xl_bylx4"] + "', \r\n"
                                + "'" + row["tc_xl_bydate4"] + "', \r\n"
                                + "'" + row["tc_xl_byo4"] + "', \r\n"
                                + "'" + row["tc_xl_byschool4"] + "', \r\n"
                                + "'" + row["tc_xl_he4"] + "', \r\n"
                                + "'" + row["tc_xl_zhy4"] + "' \r\n"
                                + " ) \r\n";

                    if (!DBAccess.RunCommand(strCommand))
                    {
                        ConvertDetail += "导入家庭成员4失败, tc_id=" + row["tc_id"].ToString() + "\r\n";
                        Debug.WriteLine("导入家庭成员4失败, tc_id=" + row["tc_id"].ToString() + "\r\n");
                        continue;
                    }
                }

                if (!string.IsNullOrWhiteSpace((string)row["tc_xl_bylx5"]))
                {
                    SequenceNumber++;
                    Guid FamilyMemberID = Guid.NewGuid();

                    //todo, 该表不需要StudentNumber， 不过留着以后看着方便
                    strCommand = "INSERT INTO StudentFamilyMembers(ID, StudentID, SequenceNumber, RelationShip, FullName, Age, Job, HealthStatus, JobOutOfHometown ) \r\n"
                                + " Values ( \r\n"
                                + "{" + FamilyMemberID + "}, \r\n"
                                + "{" + StudentID + "}, \r\n"
                                + SequenceNumber + ", \r\n"
                                + "'" + row["tc_xl_bylx5"] + "', \r\n"
                                + "'" + row["tc_xl_bydate5"] + "', \r\n"
                                + "'" + row["tc_xl_byo5"] + "', \r\n"
                                + "'" + row["tc_xl_byschool5"] + "', \r\n"
                                + "'" + row["tc_xl_he5"] + "', \r\n"
                                + "'" + row["tc_xl_zhy5"] + "' \r\n"
                                + " ) \r\n";

                    if (!DBAccess.RunCommand(strCommand))
                    {
                        ConvertDetail += "导入家庭成员5失败, tc_id=" + row["tc_id"].ToString() + "\r\n";
                        Debug.WriteLine("导入家庭成员5失败, tc_id=" + row["tc_id"].ToString() + "\r\n");
                        continue;
                    }
                }

                StudentDBL StudentDBL = new StudentDBL();
                if (!StudentDBL.InsertDefaultStudentEduInfo(StudentID))
                {
                    string Errror = "学生tc_id(" + row["tc_id"] + "), InsertDefaultStudentEduInfo失败";
                    Debug.WriteLine(Errror);
                    ConvertDetail += Errror + "\r\n";
                }
            }

            //规范化一些数据
            DBAccess.RunCommand("UPDATE Student2012 SET Nation = trim(Nation)");
            DBAccess.RunCommand("UPDATE Student2012 SET Nation = Nation+'族' WHERE Len(Nation)=1");

            return true;
        }
    }
}
